"""
Case Type   : 基础功能
Case Name   : 密文创建订阅，且密码过长
Create At   : 2022/3/1
Owner       : opentestcase026
Description :
    1.两个集群创建用户
    2.两个集群创建表
    3.创建发布(发布端执行)
    4.生成加密密码(订阅端执行)
    5.创建订阅(订阅端执行，端口只能是DN端口+1)
    6.发布端插入数据
    7.订阅端查询数据
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.数据同步
History     :
    Modified by opentestcase004 2022/09/26:与开发沟通，step7增加等待5.5s
    Modified by opentestcase012 2022/10/11:优化用例和断言,创建订阅端时加参数 with (copy_data=false)
"""
import unittest
import os
from yat.test import macro
from yat.test import Node
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant

Primary_SH = CommonSH('PrimaryDbUser')


@unittest.skipIf(3 != Primary_SH.get_node_num(), '非1+2环境不执行')
class Pubsubclass(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info("-----------this is setup-----------")
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.pri_userdb_pub = Node(node='PrimaryDbUser')
        self.pri_userdb_sub = Node(node='remote1_PrimaryDbUser')
        self.constant = Constant()
        self.commsh_pub = CommonSH('PrimaryDbUser')
        self.commsh_sub = CommonSH('remote1_PrimaryDbUser')
        self.com_pub = Common()
        self.subname1 = "sub_case152_1"
        self.pubname1 = "pub_case152_1"
        self.hba_pub = os.path.join(macro.DB_INSTANCE_PATH, 'pg_hba.conf')
        self.hba_sub = os.path.join(macro.DB_INSTANCE_PATH_REMOTE1,
                                    'pg_hba.conf')
        self.hba_bak_pub = os.path.join(
            os.path.dirname(macro.DB_INSTANCE_PATH), 'pg_hba.conf')
        self.hba_bak_sub = os.path.join(
            os.path.dirname(macro.DB_INSTANCE_PATH_REMOTE1), 'pg_hba.conf')
        self.port = str(int(self.pri_userdb_pub.db_port) + 1)
        self.wal_level = self.com_pub.show_param("wal_level")
        self.user_name1 = "u_case152_1"
        self.user_name2 = "u_case152_2"
        self.passwd = "openGauss_1234567890123456789012"
        self.user_param_u = f'-U {self.user_name1} ' \
            f'-W {macro.COMMON_PASSWD}'
        self.tb_name = 'public.t_pubsub_case152'

        text = '-----预置条件1:备份配置文件pg_hba.conf expect:状态正常-----'
        self.log.info(text)
        cmd = f'cp {self.hba_pub} {self.hba_bak_pub};'
        self.log.info(cmd)
        msg1 = self.com_pub.get_sh_result(self.pri_userdb_pub, cmd)
        cmd = f'cp {self.hba_sub} {self.hba_bak_sub};'
        self.log.info(cmd)
        msg2 = self.com_pub.get_sh_result(self.pri_userdb_sub, cmd)
        self.assertEqual('', msg1 and msg2, '执行失败:' + text)

        text = '-----预置条件2:查看发布端和订阅端的数据库状态 expect:状态正常-----'
        self.log.info(text)
        status = self.commsh_pub.get_db_cluster_status()
        self.assertIn(self.constant.CLUSTER_NORMAL_MSG, status, '执行失败:' + text)
        status = self.commsh_sub.get_db_cluster_status(
            env_path=macro.DB_ENV_PATH_REMOTE1)
        self.assertIn(self.constant.CLUSTER_NORMAL_MSG, status, '执行失败:' + text)

        text = '-----预置条件3:修改发布端pg_hba文件和配置参数wal_level,重启数据库 expect:成功-----'
        self.log.info(text)
        guc_res = self.commsh_pub.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, '',
            f'host    replication  all '
            f'{self.pri_userdb_sub.db_host}/32 sha256')
        self.log.info(guc_res)
        self.assertTrue(guc_res, '执行失败:' + text)
        if 'logical' != self.wal_level:
            result = self.commsh_pub.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG, 'wal_level=logical')
            self.log.info(result)
            self.assertTrue(result, '执行失败:' + text)
            result = self.commsh_pub.restart_db_cluster(False)
            self.assertTrue(result, '执行失败:' + text)
            status = self.commsh_pub.get_db_cluster_status()
            self.assertIn(self.constant.CLUSTER_NORMAL_MSG, status,
                          '执行失败:' + text)

        text = '-----预置条件4:订阅端修改pg_hba文件 expect:成功-----'
        self.log.info(text)
        guc_res = self.commsh_sub.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host    replication  all '
            f'{self.pri_userdb_pub.db_host}/32 sha256',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res)
        self.assertTrue(guc_res, '执行失败:' + text)

    def test_pubsub(self):
        text = "--step1:两个集群创建用户 expect:成功--"
        self.log.info(text)
        sql = f"drop user if exists {self.user_name1};" \
            f"create user {self.user_name1} with sysadmin " \
            f"identified by '{macro.COMMON_PASSWD}';"
        result = self.commsh_pub.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, result,
                      '执行失败:' + text)
        result = self.commsh_sub.execut_db_sql(sql, '', None,
                                               macro.DB_ENV_PATH_REMOTE1)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, result,
                      '执行失败:' + text)

        text = "--step2:两个集群创建表 expect:成功--"
        self.log.info(text)
        sql = f"drop table if exists {self.tb_name};" \
            f"create table {self.tb_name}(i int primary key);"
        result = self.commsh_pub.execut_db_sql(sql, self.user_param_u)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      '执行失败:' + text)
        result = self.commsh_sub.execut_db_sql(sql, self.user_param_u,
                                               None,
                                               macro.DB_ENV_PATH_REMOTE1)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      '执行失败:' + text)

        text = "--step3:创建发布(发布端执行) expect:成功--"
        self.log.info(text)
        sql = f"drop user if exists {self.user_name2};" \
            f"create user {self.user_name2} with sysadmin " \
            f"identified by '{self.passwd}';" \
            f"drop publication if exists {self.pubname1};" \
            f"create publication {self.pubname1} for all tables;"
        result = self.commsh_pub.execut_db_sql(sql,
                                               sql_type=self.user_param_u)
        self.log.info(result)
        self.assertIn(self.constant.create_pub_succ_msg, result,
                      '执行失败:' + text)
        self.assertNotIn(self.constant.SQL_WRONG_MSG[1], result,
                         '执行失败:' + text)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, result,
                      '执行失败:' + text)

        text = "--step4:生成加密密码(订阅端执行) expect:成功--"
        self.log.info(text)
        result = self.commsh_sub.execute_generate(
            macro.COMMON_PASSWD, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.assertIn('', result, '执行失败:' + text)

        text = "--step5:创建订阅(订阅端执行，端口只能是DN端口+1) expect:成功--"
        self.log.info(text)
        sql = f"drop subscription if exists {self.subname1};" \
            f"create subscription {self.subname1} connection " \
            f"'host={self.pri_userdb_pub.db_host} " \
            f"port={self.port} " \
            f"user={self.user_name2} " \
            f"dbname={self.pri_userdb_pub.db_name} " \
            f"password={self.passwd}' publication {self.pubname1} " \
            f"with (copy_data=false);"
        result = self.commsh_sub.execut_db_sql(sql, self.user_param_u, None,
                                               macro.DB_ENV_PATH_REMOTE1)
        self.log.info(result)
        self.assertNotIn(self.constant.SQL_WRONG_MSG[1], result,
                         '执行失败:' + text)
        self.assertNotIn(macro.COMMON_PASSWD, result, '执行失败:' + text)
        self.assertNotIn(self.passwd, result, '执行失败:' + text)
        self.assertIn(self.constant.create_sub_succ_msg,
                      result, '执行失败:' + text)

        text = "--step6:发布端插入数据 expect:成功--"
        self.log.info(text)
        sql = f"insert into {self.tb_name} values(999);"
        result = self.commsh_pub.execut_db_sql(sql,
                                               sql_type=self.user_param_u)
        self.log.info(result)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result,
                      '执行失败:' + text)

        text = "--step7:订阅端查询数据 expect:数据同步--"
        self.log.info(text)
        sql = f"select pg_sleep(5.5);" \
            f"select * from {self.tb_name};"
        result = self.commsh_sub.execut_db_sql(sql, self.user_param_u, None,
                                               macro.DB_ENV_PATH_REMOTE1)
        self.log.info(result)
        self.assertIn('999', result, '执行失败:' + text)
        self.assertEqual(result.count('1 row'), 2, '执行失败:' + text)

    def tearDown(self):
        self.log.info('-----清理环境-----')
        text1 = '-----订阅端和发布端分别删除订阅和表 expect:成功-----'
        self.log.info(text1)
        sql = f"drop subscription if exists {self.subname1};" \
            f"drop table if exists {self.tb_name}"
        drop_sub_result = self.commsh_sub.execut_db_sql(
            sql, self.user_param_u, None, macro.DB_ENV_PATH_REMOTE1)
        self.log.info(drop_sub_result)
        sql = f"drop publication if exists {self.pubname1};" \
            f"drop table if exists {self.tb_name};" \
            f"purge recyclebin"
        drop_pub_result = self.commsh_pub.execut_db_sql(
            sql, sql_type=self.user_param_u)
        self.log.info(drop_pub_result)

        text2 = '-----订阅端和发布端分别删除用户 expect:成功-----'
        self.log.info(text2)
        sql = f"drop user if exists {self.user_name1};" \
            f"drop user if exists {self.user_name2}"
        drop_user_pub = self.commsh_pub.execut_db_sql(sql)
        self.log.info(drop_user_pub)
        drop_user_sub = self.commsh_sub.execut_db_sql(
            sql, '', None, macro.DB_ENV_PATH_REMOTE1)
        self.log.info(drop_user_sub)

        text3 = '-----恢复pg_hba.conf文件 expect:成功-----'
        self.log.info(text3)
        cmd = f'if [[ -e {self.hba_pub} && -e {self.hba_bak_pub} ]]; ' \
            f'then rm -rf {self.hba_pub}; ' \
            f'mv {self.hba_bak_pub} {self.hba_pub}; ' \
            f'else echo not exits; fi'
        hba_msg_pub = self.com_pub.get_sh_result(self.pri_userdb_pub, cmd)
        cmd = f'if [[ -e {self.hba_sub} && -e {self.hba_bak_sub} ]]; ' \
            f'then rm -rf {self.hba_sub}; ' \
            f'mv {self.hba_bak_sub} {self.hba_sub}; ' \
            f'else echo not exits; fi'
        hba_msg_sub = self.com_pub.get_sh_result(self.pri_userdb_sub, cmd)

        text4 = '-----恢复发布端的wal_level参数值 expect:成功-----'
        self.log.info(text4)
        guc_res = self.commsh_pub.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f'wal_level={self.wal_level}')

        text5 = '-----重启数据库查看数据库状态 expect:成功-----'
        self.log.info(text5)
        res_msg_pub = self.commsh_pub.restart_db_cluster(False)
        status_pub = self.commsh_pub.get_db_cluster_status()
        self.log.info(status_pub)
        res_msg_sub = self.commsh_sub.restart_db_cluster(
            env_path=macro.DB_ENV_PATH_REMOTE1)
        status_sub = self.commsh_sub.get_db_cluster_status(
            env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(status_sub)

        self.assertIn(self.constant.drop_sub_succ_msg, drop_sub_result,
                      '执行失败' + text1)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_sub_result,
                      '执行失败' + text1)
        self.assertIn(self.constant.drop_pub_succ_msg, drop_pub_result,
                      '执行失败' + text1)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_pub_result,
                      '执行失败' + text1)
        self.assertIn('PURGE RECYCLEBIN', drop_pub_result, '执行失败' + text1)
        self.assertEqual(2, drop_user_sub.count(
            self.constant.DROP_ROLE_SUCCESS_MSG), '执行失败' + text2)
        self.assertEqual(2, drop_user_pub.count(
            self.constant.DROP_ROLE_SUCCESS_MSG), '执行失败' + text2)
        self.assertEqual('', hba_msg_pub and hba_msg_sub, '执行失败:' + text3)
        self.assertTrue(guc_res, '执行失败:' + text4)
        self.assertTrue(res_msg_pub, '执行失败:' + text5)
        self.assertTrue(res_msg_sub, '执行失败:' + text5)
        self.assertIn(self.constant.CLUSTER_NORMAL_MSG, status_pub,
                      '执行失败:' + text5)
        self.assertIn(self.constant.CLUSTER_NORMAL_MSG, status_sub,
                      '执行失败:' + text5)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
